﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace lxwmvc
{
    /// <summary>
    /// 组合查询条件
    /// </summary>
    public static class dbdatehelper
    {

        #region IDBDateHelper 成员

        //public static string dbnowdate(params object[] objs)
        //{
        //    return " getdate() ";
        //}

        //public static string safesqlvalue(string strValue)
        //{
        //    return strValue.Replace("'", "''");
        //}

        /// <summary>
        /// 生成日期比较字符串
        /// </summary>
        /// <param name="strColumn">字段</param>
        /// <param name="express">比较表达式</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public static string dbdatecompare(string strColumn, express express, string value)
        {
            string strResult = "";
            switch (express)
            {
                case express.dayuriqi:
                    strResult = " " + strColumn + " >'{0}' ";
                    break;
                case express.xiaoyuriqi:
                    strResult = " " + strColumn + " <'{0}' ";
                    break;
                case express.dayudengyuriqi:
                    strResult = " " + strColumn + " >='{0}' ";
                    break;
                case express.xiaoyudengyuriqi:
                    strResult = " " + strColumn + " <='{0}' ";
                    break;
                case express.dengyuriqi:
                    strResult = " " + strColumn + " ='{0}' ";
                    break;
                case express.budengyuriqi:
                    strResult = " " + strColumn + " <>'{0}' ";
                    break;
                
                default:
                    break;
            }
            if (strResult.Length > 0) strResult = string.Format(strResult, value);

            return strResult;
        }

        /// <summary>
        /// 这里存在一个严重的问题，已经处理  20120702
        /// </summary>
        /// <param name="column">字段</param>
        /// <param name="express1">比较表达式1</param>
        /// <param name="startDate">开始日期</param>
        /// <param name="express2">比较表达式2</param>
        /// <param name="endDate">结束日期</param>
        /// <returns></returns>
        public static string dbdatecompare(string column, express express1, string startDate, express express2, string endDate)
        {
            return "(" + dbdatecompare(column, express1, startDate) + " and " + dbdatecompare(column, express2, endDate) + ")";
        }


        /// <summary>
        /// 组合查询语句
        /// </summary>
        /// <param name="where"></param>
        /// <param name="strcolmun"></param>
        /// <param name="strvalue"></param>
        /// <param name="orand"></param>
        /// <param name="express"></param>
        public static void querystring(WHERE where, String strcolmun, String strvalue, orand orand, express express)
        {
            querystring(ref where.where, strcolmun, strvalue, orand, express);
        }

        /// <summary>
        /// 功能:组合查询语句
        /// [09.4.12 lxw]<para />
        /// </summary>
        /// <param name="strwhere">需要组合的字符串</param>
        /// <param name="strcolmun">等式左边的数据，相当于数据库对应的字段</param>
        /// <param name="strvalue">等式右边的数据, 多个数据用";"隔开</param>
        /// <param name="orand">OrAnd表示和前面的语句组合方式，如果是第一个，OrAnd不起作用</param>
        /// <param name="express">等式中间连接的表达式</param>
        public static void querystring(ref String strwhere, String strcolmun, String strvalue, orand orand, express express)
        {
            if (strvalue != null && !strvalue.Equals(""))
            {
                if (!strwhere.Equals(""))
                    strwhere += " " + Enum.GetName(typeof(orand), orand) + " ";
                else
                    strwhere += " ";

                strvalue = strvalue.Trim();

                int i = 0;
                foreach (String Str in strvalue.Split(';'))
                {
                    if (i++ > 0)
                        strwhere += " Or ";//里面循环规定必须是 or
                    else
                        strwhere += " (";

                    strwhere += String.Format(returnsql(strcolmun, express), Str);

                    if (i == strvalue.Split(';').Length) strwhere += " )";
                }
            }
        }

        /// <summary>
        /// 功能:组合时间段查询语句
        /// </summary>
        /// <param name="where"></param>
        /// <param name="strcolumn"></param>
        /// <param name="strstartdate"></param>
        /// <param name="strenddate"></param>
        /// <param name="orand"></param>
        public static void querydatetime(WHERE where, String strcolumn, string strstartdate, string strenddate, orand orand)
        {
            querydatetime(ref where.where, strcolumn, strstartdate, strenddate, orand);
        }
        

        /// <summary>
        /// 功能:组合时间段查询语句
        /// [11-03-10 20:15  周四 lxw]<para />
        /// </summary>
        /// <param name="strwhere">需要组合的字符串</param>
        /// <param name="strcolumn">等式左边的数据，相当于数据库对应的字段</param>
        /// <param name="strstartdate">起始日期</param>
        /// <param name="strenddate">结尾日期</param>
        /// <param name="orand">OrAnd表示和前面的语句组合方式，如果是第一个，OrAnd不起作用</param>
        public static void querydatetime(ref String strwhere, String strcolumn, string strstartdate, string strenddate, orand orand)
        {
            //Result retStartDate = ConvertHelper.DateTime(strStartDate);
            //Result retEndDate = ConvertHelper.DateTime(strEndDate);
            DateTime? retstartdate, retenddate;
            retstartdate = retenddate = null;
            try
            {
                retstartdate = strstartdate.todatetime("1900-1-1", true);
            }
            catch { }

            try
            {
                retenddate = strenddate.todatetime("1900-1-1", true);
            }
            catch { }

            string strDate = "";
            //如果都转换失败，返回
            //[11-03-10 20:13 lxw]
            //[09.5.19 hl]
            if (!retstartdate.HasValue && !retenddate.HasValue)
            {
                return;
            }

            //如果开始时间成功，结束时间失败，取大于等于开始时间
            //[11-03-10 20:13 lxw]
            //[09.5.19 hl]
            if (retstartdate.HasValue && !retenddate.HasValue)
            {
                //QueryString(ref strWhere, strColumn, strStartDate, orAnd, Express.DaYuDengYuRiQi);
                if (!strwhere.Equals(""))
                    strwhere += " " + Enum.GetName(typeof(orand), orand) + " ";
                else
                    strwhere += " ";

                strwhere += dbdatecompare(strcolumn, express.dayudengyuriqi, retstartdate.Value.tostr());
            }
            //如果开始时间失败，结束时间成功，取小于等于结束时间
            //[11-03-10 20:15 lxw]
            //[09.5.19 hl]
            else if (!retstartdate.HasValue && retenddate.HasValue)
            {
                if (!strwhere.Equals(""))
                    strwhere += " " + Enum.GetName(typeof(orand), orand) + " ";
                else
                    strwhere += " ";

                strwhere += dbdatecompare(strcolumn, express.xiaoyudengyuriqi, retenddate.Value.tostr());
            }
            else
            {
                //20120702 确实存在问题。只有前面的日期大于后面的日期，才交换
                if (((TimeSpan)(retstartdate.Value - retenddate.Value)).TotalSeconds > 0)
                {
                    strDate = strstartdate;
                    strstartdate = strenddate;
                    strenddate = strDate;
                }

                if (!strwhere.Equals(""))
                    strwhere += " " + Enum.GetName(typeof(orand), orand) + " ";
                else
                    strwhere += " ";

                strwhere += dbdatecompare(strcolumn, express.dayudengyuriqi, retstartdate.Value.tostr(), express.xiaoyudengyuriqi, retenddate.Value.tostr());
                //strWhere += " datediff(d," + strColumn + ",'" + strStartDate + "')<=0  and datediff(d," + strColumn + ",'" + strEndDate + "')>=0 ";
            }
        }

        /// <summary>
        /// 组合SQL语句
        /// </summary>
        /// <param name="strcolumn">字段</param>
        /// <param name="express">连接方式</param>
        /// <returns>返回组合后的SQL语句</returns>
        static String returnsql(String strcolumn, express express)
        {
            string strresult = "";
            switch (express)
            {
                case express.like:
                    strresult = " " + strcolumn + " like '%{0}%'";
                    break;
                case express.notlike:
                    strresult = " " + strcolumn + " not like '%{0}%'";
                    break;
                case express.dayushuzi:
                    strresult = " " + strcolumn + " >{0}";
                    break;
                case express.xiaoyushuzi:
                    strresult = " " + strcolumn + " <{0}";
                    break;
                case express.dayudengyushuzi:
                    strresult = " " + strcolumn + " >={0}";
                    break;
                case express.xiaoyudengyushuzi:
                    strresult = " " + strcolumn + " <={0}";
                    break;
                case express.dengyushuzi:
                    strresult = " " + strcolumn + " ={0}";
                    break;
                case express.budengyushuzi:
                    strresult = " " + strcolumn + " <>{0}";
                    break;
                case express.dengyuzifuchuan:
                    strresult = " " + strcolumn + " ='{0}'";
                    break;
                case express.budengyuzifuchuan:
                    strresult = " " + strcolumn + " <>'{0}'";
                    break;
                case express.@in:
                    strresult = " " + strcolumn + " in ({0}) ";
                    break;
                case express.notin:
                    strresult = " " + strcolumn + " not in ({0}) ";
                    break;
                case express.dengyuriqi:
                case express.budengyuriqi:
                case express.dayuriqi:
                case express.dayudengyuriqi:
                case express.xiaoyuriqi:
                case express.xiaoyudengyuriqi:
                    strresult = dbdatecompare(strcolumn, express, "{0}");
                    break;

                default:
                    break;
            }
            return strresult;
        }

        #endregion
    }
}
